﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Collections;
using System.Collections.Generic;
using Excel;

namespace Utility
{
    /// <summary>
    /// DataTableToExcel 的摘要说明
    /// </summary>
    public class DataTableToExcel
    {
        /// <summary>
        /// 由 DataSet 导出 Excel 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="FileName"></param>       
        public static void DataTableToExcelAndDownload(System.Data.DataTable dt, string FileName)
        {
            HttpResponse resp = HttpContext.Current.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
            string colHeaders = "", ls_item = "";
            int i = 0;

            DataRow[] myRow = dt.Select("");

            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
            for (i = 0; i < dt.Columns.Count; i++)
            {
                if (i == dt.Columns.Count - 1)
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\n";
                }
                else
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\t";
                }
            }
            resp.Write(colHeaders);

            foreach (DataRow row in myRow)
            {
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    if (i == dt.Columns.Count - 1)
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }
                }

                resp.Write(ls_item);
                ls_item = "";
            }

            resp.End();
        }

        /// <summary>
        /// 由 DataSet 导出 Excel ，自定义字段名 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="FileName"></param>       
        public static void DataTableToExcelAndDownload(System.Data.DataTable dt, string FileName, string[] cells)
        {
            HttpResponse resp = HttpContext.Current.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
            string colHeaders = "", ls_item = "";
            int i = 0;

            DataRow[] myRow = dt.Select("");

            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
            for (i = 0; i < cells.Length; i++)
            {
                if (i == cells.Length - 1)
                {
                    colHeaders += cells[i].ToString() + "\n";
                }
                else
                {
                    colHeaders += cells[i].ToString() + "\t";
                }
            }
            resp.Write(colHeaders);

            foreach (DataRow row in myRow)
            {
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    if (i == dt.Columns.Count - 1)
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }
                }

                resp.Write(ls_item);
                ls_item = "";
            }

            resp.End();
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="ds">数据集</param>
        /// <param name="filename">导出文件名</param>
        /// <param name="workSheetName">工作表名</param>
        /// <param name="cells">字段名</param>
        public static void ExportExcelFromDataSet(DataSet ds, string filename, string workSheetName, string[] cells)
        {
            string path = HttpContext.Current.Server.MapPath("../upload/excel/");
            string mm = path + filename;
            string downxls = "../upload/excel/" + filename;
            if (System.IO.File.Exists(downxls))
                System.IO.File.Delete(downxls);
            Excel.Application oExcel = new Excel.Application();
            try
            {
                Object oMissing = System.Reflection.Missing.Value;
                Workbooks wbs = oExcel.Workbooks;
                Workbook oBook = wbs.Add(oMissing);
                HttpResponse response = HttpContext.Current.Response;

                int lie = ds.Tables[0].Columns.Count;//列
                int hang = ds.Tables[0].Rows.Count;//行
                int i, j, t;
                for (int a = 0; a < cells.Length; a++)
                {
                    oExcel.Cells[1, a + 1] = cells[a];
                    oExcel.Cells.get_Range(oExcel.Cells[1, a + 1], oExcel.Cells[1, a + 1]).Interior.ColorIndex = 19;//设置为浅颜色，共计有56种,1黑2白3红4绿5蓝6黄7粉红9深红
                }
                Excel.Worksheet workSheet = (Excel.Worksheet)oBook.Sheets.get_Item(1);//工作表
                workSheet.Name = workSheetName;//工作表名称

                t = 1;
                for (i = 0; i < hang; i++)//内容
                {
                    for (j = 0; j < lie; j++)
                    {
                        if (j == 1 || j == 18)
                            oExcel.Cells[i + 2, t++] = (char)30 + ds.Tables[0].Rows[i][j].ToString().Trim();//数字字符串
                        else
                            oExcel.Cells[i + 2, t++] = ds.Tables[0].Rows[i][j].ToString().Trim();
                    }
                    t = 1;
                }
                oExcel.Visible = true;
                //oExcel.get_Range(oExcel.Cells, oExcel.Cells).Borders.LineStyle = 1;//绘制边框
                //oExcel.Cells.get_Range(oExcel.Cells, oExcel.Cells).Borders.LineStyle = 1;//绘制边框

                oExcel.Cells.get_Range(oExcel.Cells[1, 1], oExcel.Cells[1, lie]).Font.Bold = true;//加粗
                //oExcel.Cells.get_Range(oExcel.Cells[1, 1], oExcel.Cells[1, cells.Length]).Font.Color = System.Drawing.Color.White;
                oExcel.Cells.get_Range(oExcel.Cells[1, 1], oExcel.Cells[hang + 1, lie]).Borders.LineStyle = 1;//绘制边框
                oExcel.Cells.Font.Size = 11;//设置整个报表的字体大小
                oExcel.Cells.Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//单元格居中
                oExcel.Cells.Columns.AutoFit();//自动调整单元格宽度
                oBook.Saved = true;
                oExcel.UserControl = false;


                oExcel.ActiveWorkbook.SaveCopyAs(mm);
                oExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)oExcel);
                GC.Collect();

                //response.Redirect(downxls);
            }
            catch
            {
                oExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)oExcel);
                GC.Collect();
            }
        }
    }
}